﻿using System.Data.SqlClient;
using Ayatta.Contract.Component;
using Dapper;
using System;
using System.Linq;
using System.Data;
using Ayatta.Contract;
using Ayatta.Contract.Domain;
using System.Collections.Generic;
using Ayatta.Service.Extension;
namespace Ayatta.Service
{
    public class GameService : ServiceBase, IGameService
    {
        public GameUser GetGameUser(int userId)
        {
            var builder = new SqlBuilder().Field("*")
            .Table(Database.Game.Table.GameUser)
            .Where("[UserId]=@UserId", new { userId });

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Game))
            {
                return Query<GameUser>(connection, template).FirstOrDefault();
            }
        }

        public void UpdateGameUser(GameUser o)
        {
            const string tpl = @"if exists(select 0 from [Game.User] where [Guid]=@Guid and [UserId]=@UserId and [GameId]=@GameId and [ServerId]=@ServerId)
                                          begin
	                                        update [Game.User] set [Vip]=@Vip,[Level]=@Level,[Currency]=@Currency,[Extra]=@Extra,[ModifiedOn]=@ModifiedOn,[LastLoginOn]=@LastLoginOn where [Guid]=@Guid and [UserId]=@UserId and [GameId]=@GameId and [ServerId]=@ServerId
                                          end
                                          else
                                          begin
	                                        insert into [Game.User]values(@Guid,@UserId,@GameId,@ServerId,@RoleName,@Vip,@Level,@Currency,@Extra,@ModifiedOn,@LastLoginOn,@CreatedBy,@CreatedOn)
                                          end";
            var template = new SqlBuilder().AddTemplate(tpl, new { o.Guid, o.UserId, o.GameId, o.ServerId, o.RoleName, o.Vip, o.Level, o.Currency, o.Extra, o.ModifiedOn, o.LastLoginOn, o.CreatedBy, o.CreatedOn });

            using (var connection = GetConnection(ConnectionName.Game))
            {
                Execute(connection, template);
            }
        }

        public PagedList<GameUser> GetPagedGameUsers(int index = 1, int size = 2, int userId = 0, string guid = null, int gameId = 0, int serverId = 0, int level = 0)
        {
            var builder = new SqlBuilder().Field("*")
               .Table(Database.Game.Table.GameUser)
               .OrderBy("[Level] DESC")
               .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });

            if (!string.IsNullOrEmpty(guid))
            {
                builder.Where("[Guid] = @Guid", new { guid });
            }
            if (userId > 0)
            {
                builder.Where("[UserId]=@UserId", new { userId });
            }
            if (gameId > 0)
            {
                builder.Where("[GameId]=@GameId", new { gameId });
            }
            if (serverId > 0)
            {
                builder.Where("[ServerId]=@ServerId", new { serverId });
            }
            if (level > 0)
            {
                builder.Where("[Level]=@Level", new { level });
            }

            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Game).EnsureOpen())
            {
                return connection.PagedQuery<GameUser>(template);
            }
        }

        public Game GetGame(int id)
        {
            var builder = new SqlBuilder().Field("*")
            .Table(Database.Game.Table.Game)
            .Where("[Id]=@Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Game))
            {
                return Query<Game>(connection, template).FirstOrDefault();
            }
        }

        public IList<Game> GetGames(bool includeServers = false)
        {
            if (includeServers)
            {
                var tpl = string.Format("SELECT * FROM {0} ;SELECT * FROM {1} ;", Database.Game.Table.Game, Database.Game.Table.GameServer);

                var template = new SqlBuilder().AddTemplate(tpl);

                using (var connection = GetConnection(ConnectionName.Game))
                using (var multi = connection.QueryMultiple(template))
                {
                    return multi.Map<Game, GameServer, int>(a => a.Id, b => b.GameId, (a, b) => { a.Servers = b; }).ToList();
                }
            }
            else
            {
                var template = new SqlBuilder().Field("*").Table(Database.Game.Table.Game).AddTemplate(SqlTemplate.Select);
                using (var connection = GetConnection(ConnectionName.Game))
                {
                    return Query<Game>(connection, template).ToList();
                }
            }
        }

        public PagedList<Game> GetPagedGames(int index = 1, int size = 2, string name = null, GameType? type = null, GameCategory? category = null, bool? status = null, bool? isHot = false, bool? isRefined = false)
        {
            var builder = new SqlBuilder().Field("*")
               .Table(Database.Game.Table.Game)
               .OrderBy("id desc")
               .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });

            if (!string.IsNullOrEmpty(name))
            {
                builder.Where("[Name] LIKE @Name", new { name = name + "%" });
            }
            if (type.HasValue && type.Value > 0)
            {
                builder.Where("[Type]=@Type", new { type });
            }
            if (category.HasValue && category.Value > 0)
            {
                builder.Where("[Category]=@Category", new { category });
            }
            if (status.HasValue)
            {
                builder.Where("[Status]=@Status", new { status });
            }
            if (isHot.HasValue)
            {
                builder.Where("[IsHot]=@IsHot", new { isHot });
            }
            if (isRefined.HasValue)
            {
                builder.Where("[IsRefined]=@IsRefined", new { isRefined });
            }
            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Game).EnsureOpen())
            {
                return connection.PagedQuery<Game>(template);
            }
        }

        public bool CreateGameServer(GameServer o)
        {
            var builder = new SqlBuilder()
            .Table(Database.Game.Table.GameServer)
            .Field("([GameId],[Name],[Alias],[Icon],[Picture],[Description],[Params],[OpendOn],[JoinTrial],[StartTime],[EndTime],[Priority],[Extra],[Status],[ModifiedOn],[CreatedBy],[CreatedOn])VALUES(@GameId,@Name,@Alias,@Icon,@Picture,@Description,@Params,@OpendOn,@JoinTrial,@StartTime,@EndTime,@Priority,@Extra,@Status,@ModifiedOn,@CreatedBy,@CreatedOn)")
            .AddParameters(new { o.GameId, o.Name, o.Alias, o.Icon, o.Picture, o.Description, o.Params, o.OpendOn, o.JoinTrial, o.StartTime, o.EndTime, o.Priority, o.Extra, o.Status, o.ModifiedOn, o.CreatedBy, o.CreatedOn });
            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Game))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdateGameServer(GameServer o)
        {
            var builder = new SqlBuilder()
            .Table(Database.Game.Table.GameServer)
            .Field("[Name]=@Name", new { o.Name })
            .Field("[Alias]=@Alias", new { o.Alias })
            .Field("[Icon]=@Icon", new { o.Icon })
            .Field("[Picture]=@Picture", new { o.Picture })
            .Field("[Description]=@Description", new { o.Description })
            .Field("[Params]=@Params", new { o.Params })
            .Field("[OpendOn]=@OpendOn", new { o.OpendOn })
            .Field("[JoinTrial]=@JoinTrial", new { o.JoinTrial })
            .Field("[StartTime]=@StartTime", new { o.StartTime })
            .Field("[EndTime]=@EndTime", new { o.EndTime })
            .Field("[Priority]=@Priority", new { o.Priority })
            .Field("[Extra]=@Extra", new { o.Extra })
            .Field("[Status]=@Status", new { o.Status })
            .Field("[ModifiedOn]=@ModifiedOn", new { o.ModifiedOn })
            .Where("[Id] = @Id AND [GameId]=@GameId", new { o.Id, o.GameId });
            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Game))
            {
                return Execute(connection, template) == 1;
            }
        }

        public GameServer GetGameServer(int id, bool inclGame = false)
        {
            var builder = new SqlBuilder().Field("*")
            .Table(Database.Game.Table.GameServer + " a")
            .Where("a.[Id]=@Id", new { id });
            if (inclGame)
            {
                builder.InnerJoin(Database.Game.Table.Game + " b on a.[GameId]=b.[Id]");
            }
            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Game))
            {
                if (inclGame)
                {
                    return connection.EnsureOpen().Query<GameServer, Game, GameServer>(template.RawSql, (a, b) => {
                        a.Game = b; return a;
                    }, template.Parameters).FirstOrDefault();
                }
                return Query<GameServer>(connection, template).FirstOrDefault();
            }
        }

        public IList<GameServer> GetGameServers(int gameId)
        {
            var builder = new SqlBuilder().Field("*")
            .Table(Database.Game.Table.GameServer)
            .Where("[GameId]=@GameId", new { gameId });

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Game))
            {
                return Query<GameServer>(connection, template).ToList();
            }
        }

        public bool CreateGameArticle(GameArticle o)
        {
            var builder = new SqlBuilder()
            .Table(Database.Game.Table.GameArticle)
            .Field("([GameId],[Category],[Title],[SubTitle],[Summary],[Content],[IsParent],[ParentId],[Flag],[Status],[ModifiedOn],[CreatedBy],[CreatedOn])VALUES(@GameId,@Category,@Title,@SubTitle,@Summary,@Content,@IsParent,@ParentId,@Flag,@Status,@ModifiedOn,@CreatedBy,@CreatedOn)")
            .AddParameters(new { o.GameId, o.Category, o.Title, o.SubTitle, o.Summary, o.Content, o.IsParent, o.ParentId, o.Flag, o.Status, o.ModifiedOn, o.CreatedBy, o.CreatedOn });
            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Game))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdateGameArticle(GameArticle o)
        {
            var builder = new SqlBuilder()
            .Table(Database.Game.Table.GameArticle)
            .Field("[GameId]=@GameId", new { o.GameId })
            .Field("[Category]=@Category", new { o.Category })
            .Field("[Title]=@Title", new { o.Title })
            .Field("[SubTitle]=@SubTitle", new { o.SubTitle })
            .Field("[Summary]=@Summary", new { o.Summary })
            .Field("[Content]=@Content", new { o.Content })
            .Field("[IsParent]=@IsParent", new { o.IsParent })
            .Field("[ParentId]=@ParentId", new { o.ParentId })
            .Field("[Flag]=@Flag", new { o.Flag })
            .Field("[Status]=@Status", new { o.Status })
            .Field("[ModifiedOn]=@ModifiedOn", new { o.ModifiedOn })
            .Where("[Id] = @Id", new { o.Id });
            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Game))
            {
                return Execute(connection, template) == 1;
            }
        }

        public GameArticle GetGameArticle(int id)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Game.Table.GameArticle)
                .Where("[Id]=@Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Game))
            {
                return Query<GameArticle>(connection, template).FirstOrDefault();
            }
        }

        public IList<GameArticle> GetGameArticles(int num = 10, int? gameId = null, int? flag = null)
        {
            var builder = new SqlBuilder().Field("*").Table(Database.Game.Table.GameArticle);
            if (gameId.HasValue && gameId.Value > 0)
            {
                builder.Where("[GameId]=@GameId", new { gameId });
            }
            if (flag.HasValue && flag.Value > 0)
            {
                builder.Where("[Flag]=@Flag", new { flag });
            }
            var template = builder.AddTemplate(SqlTemplate.SelectTopN, new { TakeSize = num });
            using (var connection = GetConnection(ConnectionName.Game))
            {
                return Query<GameArticle>(connection, template).ToList();
            }
        }

        public IList<GameArticle> GetGameArticlesByParentId(int parentId)
        {
            var builder = new SqlBuilder().Field("*").Table(Database.Game.Table.GameArticle);
            if (parentId > 0)
            {
                builder.Where("[ParentId]=@ParentId", new { parentId });
            }

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Game))
            {
                return Query<GameArticle>(connection, template).ToList();
            }
        }

        public PagedList<GameArticle> GetPagedGameArticle(int index = 1, int size = 50, int id = 0, int parentId = 0, string title = null, int? gameId = null,
                                            GameArticleCategory? category = null, GameArticleStatus? status = null, int? flag = null, DateTime? date = null)
        {
            var builder = new SqlBuilder().Field("*")
            .Table(Database.Game.Table.GameArticle)
            .OrderBy("id desc")
            .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });

            if (id > 0)
            {
                builder.Where("[Id]=@Id", new { id });
            }
            if (parentId > 0)
            {
                builder.Where("[ParentId]=@ParentId", new { parentId });
            }
            if (!string.IsNullOrEmpty(title))
            {
                builder.Where("[Title] LIKE @Title", new { title = title + "%" });
            }
            if (gameId.HasValue && gameId.Value > 0)
            {
                builder.Where("[GameId]=@GameId", new { gameId });
            }
            if (category.HasValue && category.Value > 0)
            {
                builder.Where("[Category]=@Category", new { category });
            }
            if (status.HasValue)
            {
                builder.Where("[Status]=@Status", new { status });
            }
            if (flag.HasValue)
            {
                builder.Where("[Flag]=@Flag", new { flag });
            }
            if (date.HasValue)
            {
                var temp = date.Value;
                var startDate = new DateTime(temp.Year, temp.Month, temp.Day);
                var endDate = startDate.AddDays(1);
                builder.Where("[CreatedOn]>=@StartDate AND [CreatedOn]<@EndDate", new { startDate, endDate });
            }
            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Game).EnsureOpen())
            {
                return connection.PagedQuery<GameArticle>(template);
            }
        }

        public GameTrial GetGameTrial(int id)
        {
            var builder = new SqlBuilder().Field("*")
            .Table(Database.Game.Table.GameTrial)
            .Where("[Id]=@Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Game))
            {
                return Query<GameTrial>(connection, template).FirstOrDefault();
            }
        }

        public IList<GameTrial> GetGameTrials(int serverId)
        {
            var builder = new SqlBuilder().Field("*")
            .Table(Database.Game.Table.GameTrial)
            .Where("[ServerId]=@ServerId", new { serverId });

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Game))
            {
                return Query<GameTrial>(connection, template).ToList();
            }

        }

        public bool CreateGameTrial(GameTrial o)
        {
            var builder = new SqlBuilder()
            .Table(Database.Game.Table.GameTrial)
            .Field("([GameId],[ServerId],[Level],[Integral],[CreatedBy],[CreatedOn])VALUES(@GameId,@ServerId,@Level,@Integral,@CreatedBy,@CreatedOn)")
            .AddParameters(new { o.GameId, o.ServerId, o.Level, o.Integral, o.CreatedBy, o.CreatedOn });
            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Game))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdateGameTrial(GameTrial o)
        {
            var builder = new SqlBuilder()
            .Table(Database.Game.Table.GameTrial)
            .Field("[Level]=@Level", new { o.Level })
            .Field("[Integral]=@Integral", new { o.Integral })
            .Where("[Id] = @Id  AND [GameId]=@GameId AND [ServerId]=@ServerId", new { o.Id, o.GameId, o.ServerId });
            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Game))
            {
                return Execute(connection, template) == 1;
            }
        }

        public IList<int> GetGameTrialHistorys(int userId, int serverId)
        {
            var builder = new SqlBuilder().Field("*")
           .Table(Database.Game.Table.GameTrialHistory)
           .Where("[UserId]=@UserId AND [ServerId]=@ServerId", new { userId, serverId });

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Game))
            {
                return Query<int>(connection, template).ToList();
            }
        }

        public int GetGameTrialIntegral(int userId, int trialId)
        {
            const string sp = "[Proc.Game.Trial.Integral.Get]";
            var param = new DynamicParameters(new { userId, trialId });
            param.Add("status", 1, DbType.Int32, ParameterDirection.Output);
            using (var connection = GetConnection(ConnectionName.Game))
            {
                Execute(connection, sp, param);
                return param.Get<int>("@status");
            }
        }

        public PagedList<GameTrialHistory> GetPagedGameTrialHistories(int index = 1, int size = 2, int? userId = null,
                                                                     int? serverId = null, int? level = null, DateTime? date = null)
        {
            var builder = new SqlBuilder().Field("*")
               .Table(Database.Game.Table.GameTrialHistory)
               .OrderBy("id desc")
               .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });


            if (userId.HasValue && userId.Value > 0)
            {
                builder.Where("[UserId]=@UserId", new { userId });
            }
            if (serverId.HasValue && serverId.Value > 0)
            {
                builder.Where("[ServerId]=@ServerId", new { serverId });
            }
            if (level.HasValue && level.Value > 0)
            {
                builder.Where("[Level]=@Level", new { level });
            }
            if (date.HasValue)
            {
                var temp = date.Value;
                var startDate = new DateTime(temp.Year, temp.Month, temp.Day);
                var endDate = startDate.AddDays(1);
                builder.Where("[CreatedOn]>=@StartDate AND [CreatedOn]<@EndDate", new { startDate, endDate });
            }
            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Game).EnsureOpen())
            {
                return connection.PagedQuery<GameTrialHistory>(template);
            }
        }

        #region
        public string GetGameNoviceCardCode(int gameId, int serverId, int userId, string username)
        {
            const string sp = "[Proc.Game.Novice.Card.Get]";
            var param = new DynamicParameters(new { gameId, serverId, userId, username });

            using (var connection = GetConnection(ConnectionName.Game).EnsureOpen())
            {
                return
                    connection.Query<string>(sp, param, commandType: CommandType.StoredProcedure)
                              .FirstOrDefault();
            }
        }

        public bool UploadGameNoviceCard(int gameId, string[] codes)
        {
            var time = DateTime.Now;
            var tableName = Database.Game.Table.GameNoviceCard;
            var dt = new DataTable(tableName);
            dt.Columns.Add(new DataColumn("Id", typeof(int)));
            dt.Columns.Add(new DataColumn("GameId", typeof(int)));
            dt.Columns.Add(new DataColumn("Code", typeof(string)));
            dt.Columns.Add(new DataColumn("UserId", typeof(int)));
            dt.Columns.Add(new DataColumn("ServerId", typeof(int)));
            dt.Columns.Add(new DataColumn("Extra", typeof(string)));
            dt.Columns.Add(new DataColumn("Status", typeof(byte)));
            dt.Columns.Add(new DataColumn("ModifiedBy", typeof(string)));
            dt.Columns.Add(new DataColumn("ModifiedOn", typeof(DateTime)));
            dt.Columns.Add(new DataColumn("CreatedBy", typeof(string)));
            dt.Columns.Add(new DataColumn("CreatedOn", typeof(DateTime)));

            foreach (var code in codes)
            {
                var row = dt.NewRow();
                row.SetField(0, 0);
                row.SetField(1, gameId);
                row.SetField(2, code);
                row.SetField(3, 0);
                row.SetField(4, 0);
                row.SetField(5, string.Empty);
                row.SetField(6, 2);
                row.SetField(7, string.Empty);
                row.SetField(8, DBNull.Value);
                row.SetField(9, string.Empty);
                row.SetField(10, time);
                dt.Rows.Add(row);
            }
            try
            {
                using (var connection = GetConnection(ConnectionName.Game) as SqlConnection)
                {
                    if (connection != null)
                    {
                        using (var bulkCopy = new SqlBulkCopy(connection))
                        {
                            connection.Open();
                            bulkCopy.BulkCopyTimeout = 30;
                            bulkCopy.DestinationTableName = tableName;
                            bulkCopy.WriteToServer(dt);
                            return true;
                        }
                    }
                }
            }
            catch (Exception)
            {
                return false;
            }
            return false;
        }

        public int CheckGameNoviceCard(int gameId)
        {
            const string sql = @"with temp as(
            select min(Id) as Id from [Game.Novice.Card]
            where [GameId]=@gameId
            group by [Code])
            update [Game.Novice.Card] set [Status] =0 where [Status]<>1 and [Id] in (select Id from temp)";
            var template = new SqlBuilder().AddTemplate(sql, new { gameId });
            using (var connection = GetConnection(ConnectionName.Game))
            {
                return Execute(connection, template);
            }
        }

        public PagedList<GameNoviceCard> GetPagedGameNoviceCard(int index = 1, int size = 50, int gameId = 0, int serverId = 0,
                                                        int? userId = null, byte? status = null, DateTime? date = null)
        {
            var builder = new SqlBuilder().Field("*")
               .Table(Database.Game.Table.GameNoviceCard)
               .OrderBy("[Status] ASC")
               .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });

            if (userId.HasValue && userId.Value > 0)
            {
                builder.Where("[UserId]=@UserId", new { userId });
            }
            if (status.HasValue && status.Value > 0)
            {
                builder.Where("[Status]=@Status", new { status });

                if (serverId > 0)
                {
                    if (status == 2)
                    {
                        builder.Where("[ServerId]=@ServerId", new { serverId });
                    }
                    else
                    {
                        builder.Where("[GameId]=@GameId", new { gameId });
                    }
                }
            }
            if (date.HasValue)
            {
                var temp = date.Value;
                var startDate = new DateTime(temp.Year, temp.Month, temp.Day);
                var endDate = startDate.AddDays(1);
                builder.Where("[ModifiedOn] IS NOT NULL AND [ModifiedOn]>=@StartDate AND [ModifiedOn]<@EndDate", new { startDate, endDate });
            }
            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Game).EnsureOpen())
            {
                return connection.PagedQuery<GameNoviceCard>(template);
            }
        }
        #endregion

        #region Implementation of IService

        public int GetCachedSqlCount()
        {
            throw new NotImplementedException();
        }

        public List<Tuple<string, string, int>> GetCachedSql()
        {
            throw new NotImplementedException();
        }

        public void PurgeQueryCache()
        {
            throw new NotImplementedException();
        }

        public int Test()
        {
            return 1;
        }

        #endregion
    }
}